Like variables, datasets are be used to represent data in a task that may be different each time a task runs. But unlike standard variables, datasets can contain multiple rows and columns. This is useful when retrieving data that describes information that describes an object or objects, or represents a collection of data such as a database or spreadsheet. Only one row of a dataset may be accessed at a time. To advance rows, the Loop Dataset action must be used. Furthermore, when using datasets, the column name must be specified after the dataset name followed by a period. (Example: %datasetname.columnname% )
A dataset is a multiple column, multiple row container object. The SQL Query action is an example of an action that creates and populates a dataset, the fields contained within that dataset are determined by the query that was executed. For example if the following query is executed:
SELECT firstname, lastname, company from customer where city='Los Angeles';
Then the following data set would be generated:
datasetname
|--firstname
|--lastname
|--company
along with the standard fields included in every dataset
|--CurrentRow (returns the current row number of the dataset)
|--TotalRows (returns the total rows contained in the dataset)
|--TotalColumns (returns the total columns contained in the dataset)
|--ExecutionDate (returns
the date and time the dataset was created)
A record (row) is created for each record (row) that is retrieved from the server. To access this data use the Loop Dataset Action <AMLOOPDATASET> to loop through the records, inside the loop you can extract the data from the field of your choice (from the current record) by using an embedded expression such as the one that follows:
%mydatasetname.firstname%
or you could combine two fields together like this:
%mydatasetname.firstname + ' ' + mydatasetname.lastname%
Expressions such as these can be used in any parameter in any action. So, to display the data in a message box the AML code would look like this:
<AMMESSAGEBOX MESSAGETEXT="%mydatasetname.firstname%" WINDOWTITLE="The firstname of the current record is">
At runtime the text %mydatasetname.firstname% is replaced by the contents of the subject of the current record.
The percentage signs (%) at the beginning and end of the variable name tell AutoMateÖ that the text in-between the % is an expression and should not be taken literally. Instead, it is replaced with the current contents of that column in the current row at runtime.
(Copy and paste into the Task Builder to use)
<!--- Demonstrates using SQL query with Loop Dataset.
Gets all customer where city field is San Diego. Then writes names to
a file, to make this task work change the SQL Query connection string
and query to match your database--->
<AMSQLQUERY CONNECTIONSTRING="19y9I4LjuL6U1z5kWV9qNz7f2GrLdS16HzZ2IABihh5oydIvZvKxirNVsXfr02s5+YSmraH478OnG3fyNDrnr4k2lmJKCFqoQ/poQgCblLO4gyfnpETTF3SS5ARqugxsajhx7hYL0OypO7YoBsI0EMMvOE56AM64O"
RESULTDATASET="myresults">SELECT firstname, lastname from
customer where city='San Diego';</AMSQLQUERY>
<AMMESSAGEBOX>%myresults.TotalRows% customers found</AMMESSAGEBOX>
<AMLOOPDATASET DATASET="myresults">
<AMFILEWRITE
FILE="c:\customerlist.txt">%myresults.firstname% %myresults.lastname%</AMFILEWRITE>
</AMLOOP>
<!--- This example checks the pop3 mailbox and if a message
contains the subject 'Get out of Debt' then it is deleted. To make this
task work - modify both POP3 steps with the proper server and account
information --->
<AMPOP3MAIL RESULTDATASET="themessages" SERVER="mail.server.com"
USERNAME="username" PASSWORD="1Zb00y6hQv2ZqWN7qDphOEw==">
<AMLOOPDATASET DATASET="themessages">
<AMIFCONTAINS
TEXT="%themessages.Subject%" SUBSTRING="get out of debt">
<AMMESSAGEBOX>Deleting
message with %themessages.subject%</AMMESSAGEBOX>
<AMPOP3MAIL
ACTION="remove_message" MESSAGE="%themessages.currentrow%"
RESULTDATASET="themessages" SERVER="mail.server.com"
USERNAME="username" PASSWORD="1Zb00y6hQv2ZqWN7qDphOEw==">
</AMIF>
</AMLOOP>
See Also: